//
//  SQManger.swift
//  DemoUI
//
//  Created by 白瑾浩 on 2022/1/12.
//

import Foundation
import UIKit
import SQLite3

class SQManger: NSObject {
    var db:OpaquePointer? = nil
    
    static let instance = SQManger()
    
    class func shareInstence() -> SQManger {
        return instance
    }
    
    func openDB() -> Bool {
        let filePath = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last!
        print(filePath)
        let file = filePath + "/test.sqlite"
        let cfile = file.cString(using: String.Encoding.utf8)
        let state = sqlite3_open(cfile,&db)
        if state != SQLITE_OK{
            print("打开数据库失败")
            return false
        }
        //创建表
        return creatTable()
    }
    
    func creatTable() -> Bool {
        let sql = "CREATE TABLE IF NOT EXISTS 't_student' ('id' integer NOT NULL PRIMARY KEY AUTOINCREMENT,'name' text,'age' integer );"
        return execSql(sql: sql)
    }
    
    func execSql(sql:String) -> Bool {
        let csql = sql.cString(using: String.Encoding.utf8)
        return sqlite3_exec(db, csql, nil, nil, nil) == SQLITE_OK
    }
    
    func updataData(sql:String) -> Bool {
        let csql = (sql.cString(using: String.Encoding.utf8))!
        if sqlite3_exec(db,csql, nil, nil, nil) != SQLITE_OK {
            return false
        }
        return true
    }
    func deleteData(sql:String) {
        
        if SQManger.shareInstence().execSql(sql: sql) {
            print("删除成功")
        }else{
            print("删除失败")
        }
    }
    func querySql(sql:String) -> [Any]? {
        var stmt:OpaquePointer? = nil
        let csql = (sql.cString(using: String.Encoding.utf8))!
        
        if sqlite3_prepare(db, csql, -1, &stmt, nil) != SQLITE_OK {
            print("未准备好")
            return nil
        }
        //准备好
        var tempArr = [Any]()
        
        while sqlite3_step(stmt) == SQLITE_ROW {
            
            let name = String.init(cString: sqlite3_column_text(stmt, 0)!)
            let age = String.init(cString: sqlite3_column_text(stmt, 1)!)
            
            let model = Student.init(name: name, age: Int(age) ?? 0)
            tempArr.append(model)
        }
        return tempArr
    }
    
    func querySql(kdyWords:String) -> [Any]? {
        var stmt:OpaquePointer? = nil
        let sql = "SELECT name,age FROM t_student WHERE name LIKE '%\(kdyWords)%' OR age LIKE '%\(kdyWords)%';"
        let csql = (sql.cString(using: String.Encoding.utf8))!
        
        if sqlite3_prepare(db, csql, -1, &stmt, nil) != SQLITE_OK {
            print("未准备好")
            return nil
        }
        //准备好
        var tempArr = [Any]()
        
        while sqlite3_step(stmt) == SQLITE_ROW {
            
            let name = String.init(cString: sqlite3_column_text(stmt, 0)!)
            let age = String.init(cString: sqlite3_column_text(stmt, 1)!)
            
            let model = Student.init(name: name, age: Int(age) ?? 0)
            tempArr.append(model)
        }
        return tempArr
    }
}
